package com.buaa.utility.util;

import java.io.File;
import java.net.URL;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import com.buaa.utility.services.DBService;
import com.buaa.utility.services.ServicesFactory;


public class DBUtil {

	public static Map<String, String> map;

	public static String XmlToSql(String text) {
		if ((text == null) || ("".equals(text)))
			return "";
		return text.replaceAll("&amp;", "&").replaceAll("&gt;", ">")
				.replaceAll("&lt;", "<").replaceAll("&quot;", "\"").replaceAll(
						"&#39;", "'");
	}
	public static String[] XmlToSqls(String text) {
		String[] sqls;
		if(text.contains(";")){
			sqls = text.split(";");
			return sqls;
		}else
			return null;
		
	}

	public static Map<String, String> getXMLSql(String xml) {
		if (map == null) {
			map = new HashMap<String, String>();
		}
		
		URL file = DBUtil.class.getResource(xml);
		
		try {
			SAXReader saxReader = new SAXReader();
			Document document = saxReader.read(file);
			Element root = document.getRootElement();
			List nodes = root.elements("sql");
			for (Iterator it = nodes.iterator(); it.hasNext();) {
				Element sql = (Element) it.next();
				String id = sql.attributeValue("id");
				String context = XmlToSql(sql.getText().trim());
				map.put(id, context);
			}
		} catch (DocumentException e) {
			System.out.println(e.getMessage());
		}
		return map;
	}

	public static String formatSQL(String id,Object[] args){
		String sql = map.get(id);//.toString();
		sql = XmlToSql(sql);
		if(args.length!=0){
			sql = String.format(sql, (Object[]) args);
		}
		return sql;
	}
	
	public static List<Map<String, String>> query(String prefix, String id,
			Object[] args) throws SQLException {
		Map m = new Hashtable();
		if (prefix != null && !prefix.equals(""))
			m.put(ServicesFactory.DBKEYNAME, prefix);
		DBService db = ServicesFactory.getDBService(m);
		return query(db, id, args);
	}
	
	public static List<Map<String, String>> query(DBService db, String id,
			Object[] args) throws SQLException {
		// 1 format sql by args
		String sql = formatSQL(id,args);
		// 2 DBService.query(sql)
		List<Map<String, String>> list = db.query(sql);
		return list;
	}

	public static List<Map<String, String>> query(String id, Object[] args) throws SQLException {
		return query((String) null, id, args);
	}
	
	public static Map<String,String> selectSingleRow(DBService db,String table,String condition) throws SQLException{
		List<Map<String,String>> list = getSelectMap(db,table,condition);
		if(list!=null && list.size()>0)
			return list.get(0);
		else
			return null;
	}
	public static List<Map<String, String>> getSelectMap(DBService db,
			String table, String condition) throws SQLException {
		String sql = getSelectSql(table, condition);
		if (sql != null && !"".equals(sql))
			return db.query(sql);
		else
			return null;
	}

	public static String getSelectSql(String table, String condition) {
		StringBuffer sql = new StringBuffer();

		sql.append("select * from ").append(table);
		if (condition != null && !"".equals(condition)) {
			sql.append(" where ").append(condition);
		}
		return sql.toString();
	}
	
	public static String getMinId(DBService db,String table, String field) throws SQLException{
		String sql = getMinIdSql(db,table,field);
		if(sql != null && !"".equals(sql)){
			List<Map<String, String>> list = db.query(sql);
			return list.get(0).get(field);
		}else
			return null;
	}
	
	public static String getMinIdSql(DBService db,String table,String field){
		StringBuffer sql = new StringBuffer();
		
		if(field != null && !"".equals(field)){
			sql.append("select min(");
			sql.append("to_number(").append(field).append(")) as ").append(field);
			sql.append(" from ").append(table);
		}
		return sql.toString();
	}
	
	public static String getMaxId(DBService db,String table, String field) throws SQLException{
		String sql = getMaxIdSql(db,table,field);
		if(sql != null && !"".equals(sql)){
			List<Map<String, String>> list = db.query(sql);
			return list.get(0).get(field);
		}else
			return null;
	}
	
	public static String getMaxIdSql(DBService db,String table,String field){
		StringBuffer sql = new StringBuffer();
		
		if(field != null && !"".equals(field)){
			sql.append("select max(");
			sql.append("to_number(").append(field).append(")) as ").append(field);
			sql.append(" from ").append(table);
		}
		return sql.toString();
	}
	
	public static Map<String,List<Map<String,String>>> buildTree(List<Map<String,String>> list,String parField){
		Map<String,List<Map<String,String>>> result = new HashMap<String,List<Map<String,String>>>();
		
		for (int i = 0; i < list.size(); i++) {
			Map row = list.get(i);
			String pid = StringUtil.getString((String)row.get(parField));
			List<Map<String,String>> subs = result.get(pid);
			if(subs==null){
				subs = new ArrayList<Map<String,String>>();
				result.put(pid,subs);
			}
			subs.add(row);
		}
		
		return result;
	}
	
	public static List<Map<String,String>> getChildList(List<Map<String, String>> list, String fieldname,String id) {
		List<Map<String,String>> listMap = new ArrayList<Map<String,String>>();
		for (int i = 0; i < list.size(); i++) {
			Map<String, String> branchMap = list.get(i);
			if (id.trim().equals(branchMap.get(fieldname).trim())) {
				listMap.add(branchMap);
			}
		}
		return listMap;
	}
	
	public static boolean insertMap(String table,Map<String,String> map){
		return insertMap((String)null,table,map);
	}
	public static boolean insertMap(String prefix, String table,
			Map<String,String> map) {
		Map m = new Hashtable();
		if (prefix != null && !prefix.equals(""))
			m.put(ServicesFactory.DBKEYNAME, prefix);
		DBService db = ServicesFactory.getDBService(m);
		return insertMap(db,table,map);
	}
	public static boolean insertMap(DBService db, String table,
			Map<String, String> map) {
		String sql = getInsertMapSql(table, map,db.getFlag());
		if (sql != null && !"".equals(sql))
			return db.update(sql);
		else
			return false;
	}
	
	public static boolean insertMaps(DBService db,String table,List<Map<String,String>> maps){
		StringBuffer sqls = new StringBuffer();
		for (int i = 0; i < maps.size(); i++) {
			sqls.append(getInsertMapSql(table,maps.get(i),db.getFlag())).append(" ");
		}
		if(sqls.length()>0){
			return db.update(sqls.toString());
		}else
			return false;
	}

	public static String getInsertMapSql(String table, Map<String, String> map,int flag) {
		StringBuffer sql = new StringBuffer("insert into ");
		sql.append(table).append(" (");

		String[] fields = new String[map.size()];
		Iterator<String> i = map.keySet().iterator();
		int j = 0;
		while (i.hasNext()) {
			fields[j] = i.next();
			if (sql.charAt(sql.length() - 1) != '(') {
				sql.append(",");
			}
			if(flag==DBService.FLAG_IS_MYSQL)
				sql.append("`").append(fields[j++]).append("`");
			else
				sql.append(fields[j++]);
		}
		sql.append(") values (");
		for (String f : fields) {
			String v = map.get(f);
			if (sql.charAt(sql.length() - 1) != '(') {
				sql.append(",");
			}
			sql.append("'").append(v).append("'");
		}

		sql.append(")");
		return sql.toString();
	}

}
